import json
import requests
import pymysql

# 连接数据库
DBHOST = 'localhost'
DBUSER = 'root'
DBPSWD = '123456'
DBNAME = 'advisory_all'
db = pymysql.connect(user=DBUSER, password=DBPSWD, host=DBHOST, database=DBNAME)
cur = db.cursor()

# post参数
url = 'https://api.github.com/graphql'
api_token = "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"
headers = {'Authorization': 'token %s' % api_token}

list1 = []
# 2021/08/18 一共2245条记录，查询第100、200……2000的游标值,并存入数组中
# 先查第一个
jsonq1 = {"query": "query { securityVulnerabilities(ecosystem: NPM, first: 100) {edges{cursor}}}"}
r1 = requests.post(url=url, json=jsonq1, headers=headers)
rs1 = r1.text  # 将返回的response类型转换为字符串
rd1 = json.loads(rs1)  # 将字符串转换为字典，方便按项存储数据
c = rd1['data']['securityVulnerabilities']['edges'][99]['cursor']
list1.append(c)

# 再按每100个进行查询
for i in range(30):
    jsonq2 = {
        "query": "query { securityVulnerabilities(ecosystem: NPM, after: \"%s\", first: 100) {edges{cursor}}}" % c}
    r2 = requests.post(url=url, json=jsonq2, headers=headers)
    rs2 = r2.text  # 将返回的response类型转换为字符串
    rd2 = json.loads(rs2)  # 将字符串转换为字典，方便按项存储数据
    b = rd2['data']['securityVulnerabilities']['edges']
    # 判断如果不到100个，就停止了
    if len(b) == 100:
        c = rd2['data']['securityVulnerabilities']['edges'][99]['cursor']
        list1.append(c)
    else:
        print("it's over")
        break

# 创建数据库
# 首先查询前100个
jsonq3 = {
    "query": "query { securityVulnerabilities(ecosystem: NPM, first: 100) {nodes {advisory {"
             "databaseId ghsaId summary description references{ url } withdrawnAt } package { name } severity "
             "vulnerableVersionRange } } }"}
r = requests.post(url=url, json=jsonq3, headers=headers)

# 首次创建数据表
if r.status_code == 200:  # 判断API是否成功连接
    rs = r.text  # 将返回的response类型转换为字符串
    rd = json.loads(rs)  # 将字符串转换为字典，方便按项存储数据
    for j in rd['data']['securityVulnerabilities']['nodes']:
        b_withDrawnMark = j['advisory']['withdrawnAt']  # b开头变量表示来自于github数据库的
        #   判断该漏洞是否已过时废弃，若未废弃则存储各项信息，若废弃，则只存储项目名
        if b_withDrawnMark is None:
            #   判断ghasid是否存在，若已存在，则说明有多个受影响范围，只更新该属性；若不存在，则新增，并把各项属性存入
            databaseIdo = j['advisory']['databaseId']
            vulnerableVersionRangeo = j['vulnerableVersionRange']

            sqlQuery2 = "SELECT `databaseId` FROM advisory WHERE `databaseId`=%s"
            cur.execute(sqlQuery2, databaseIdo)
            result2 = cur.fetchall()
            if result2:
                sqlQuery3 = "SELECT `vulnerableVersionRange` FROM advisory WHERE `databaseId`=%s "
                cur.execute(sqlQuery3, databaseIdo)
                result3 = cur.fetchall()
                if vulnerableVersionRangeo in result3[0][0]:
                    continue
                else:
                    vulnerableVersionRangeo = result3[0][0] + '||' + vulnerableVersionRangeo
                    vulnerableVersionRangeo = vulnerableVersionRangeo.replace(',', '')  # 去掉受影响范围的逗号
                    sqlUpdate1 = "update advisory set `vulnerableVersionRange`=%s where `databaseId`=%s"
                    value4 = (vulnerableVersionRangeo, databaseIdo)
                    cur.execute(sqlUpdate1, value4)
                    db.commit()
            else:
                nameo = j['package']['name']
                ghsaIdo = j['advisory']['ghsaId']
                summaryo = j['advisory']['summary']
                descriptiono = j['advisory']['description']

                referencesdict = j['advisory']['references']  # 是字典，需要转换为字符串
                referenceso = ''
                for k in referencesdict:
                    referenceso = referenceso + k['url'] + '\n'

                severityo = j['severity']

                vulnerableVersionRangeo = vulnerableVersionRangeo.replace(',', '')  # 去掉受影响范围的逗号

                sqli2 = 'INSERT INTO `advisory`(`databaseId`,`name`,`ghsaId`,`summary`,`description`,' \
                        '`references`,`severity`,`vulnerableVersionRange`) VALUES(%s,%s,%s,%s,%s,%s,%s,%s) '
                value2 = (databaseIdo, nameo, ghsaIdo, summaryo, descriptiono, referenceso, severityo,
                          vulnerableVersionRangeo)
                cur.execute(sqli2, value2)
                db.commit()
else:
    print('API连接不成功')

# 接着之后的每100个查询一次
for i in list1:
    jsonq4 = {
        "query": "query { securityVulnerabilities(ecosystem: NPM, after: \"%s\",first: 100) {nodes {advisory {"
                 "databaseId ghsaId summary description references{ url } withdrawnAt } package { name } severity "
                 "vulnerableVersionRange } } }" % i}
    r = requests.post(url=url, json=jsonq4, headers=headers)

    # 往数据表增加数据
    if r.status_code == 200:  # 判断API是否成功连接
        rs = r.text  # 将返回的response类型转换为字符串
        rd = json.loads(rs)  # 将字符串转换为字典，方便按项存储数据
        for j in rd['data']['securityVulnerabilities']['nodes']:
            b_withDrawnMark = j['advisory']['withdrawnAt']  # b开头变量表示来自于github数据库的
            #   判断该漏洞是否已过时废弃，若未废弃则存储各项信息，若废弃，则只存储项目名
            if b_withDrawnMark is None:
                #   判断ghasid是否存在，若已存在，则说明有多个受影响范围，只更新该属性；若不存在，则新增，并把各项属性存入
                databaseIdo = j['advisory']['databaseId']
                vulnerableVersionRangeo = j['vulnerableVersionRange']

                sqlQuery2 = "SELECT `databaseId` FROM advisory WHERE `databaseId`=%s"
                cur.execute(sqlQuery2, databaseIdo)
                result2 = cur.fetchall()
                if result2:
                    sqlQuery3 = "SELECT `vulnerableVersionRange` FROM advisory WHERE `databaseId`=%s "
                    cur.execute(sqlQuery3, databaseIdo)
                    result3 = cur.fetchall()
                    if vulnerableVersionRangeo in result3[0][0]:
                        continue
                    else:
                        vulnerableVersionRangeo = result3[0][0] + '||' + vulnerableVersionRangeo
                        vulnerableVersionRangeo = vulnerableVersionRangeo.replace(',', '')  # 去掉受影响范围的逗号
                        sqlUpdate1 = "update advisory set `vulnerableVersionRange`=%s where `databaseId`=%s"
                        value4 = (vulnerableVersionRangeo, databaseIdo)
                        cur.execute(sqlUpdate1, value4)
                        db.commit()
                else:
                    nameo = j['package']['name']
                    ghsaIdo = j['advisory']['ghsaId']
                    summaryo = j['advisory']['summary']
                    descriptiono = j['advisory']['description']

                    referencesdict = j['advisory']['references']  # 是字典，需要转换为字符串
                    referenceso = ''
                    for k in referencesdict:
                        referenceso = referenceso + k['url'] + '\n'

                    severityo = j['severity']

                    vulnerableVersionRangeo = vulnerableVersionRangeo.replace(',', '')  # 去掉受影响范围的逗号

                    sqli2 = 'INSERT INTO `advisory`(`databaseId`,`name`,`ghsaId`,`summary`,`description`,' \
                            '`references`,`severity`,`vulnerableVersionRange`) VALUES(%s,%s,%s,%s,%s,%s,%s,%s) '
                    value2 = (databaseIdo, nameo, ghsaIdo, summaryo, descriptiono, referenceso, severityo,
                              vulnerableVersionRangeo)
                    cur.execute(sqli2, value2)
                    db.commit()
    else:
        print('API连接不成功')

db.close()
'''list1中的每个元素样例（list1[0]） {
'advisory': {
    'databaseId': 2076, 
    'ghsaId': 'GHSA-gqgv-6jq5-jjj9', 
    'summary': 'Prototype Pollution Protection Bypass in qs', 
    'description': "Affected version of `qs` are vulnerable to Prototype Pollution 
because it is possible to bypass the protection. The `qs.parse` function fails to properly prevent an object's 
prototype to be altered when parsing arbitrary input. Input containing `[` or `]` may bypass the prototype pollution 
protection and alter the Object prototype. This allows attackers to override properties that will exist in all 
objects, which may lead to Denial of Service or Remote Code Execution in specific circumstances.\n\n\n## 
Recommendation\n\nUpgrade to 6.0.4, 6.1.2, 6.2.3, 6.3.2 or later.", 
    'references': [{'url': 
'https://nvd.nist.gov/vuln/detail/CVE-2017-1000048'}, {'url': 'https://github.com/advisories/GHSA-gqgv-6jq5-jjj9'}], 
    'withdrawnAt': None}, 

'package': {
    'name': 'qs'}, 
'severity': 'HIGH', 
'vulnerableVersionRange': '>= 6.3.0, < 6.3.2'} 
'''
